package h_eekwan.service;

import h_eekwan.interfaces.P_StudentService;
import h_eekwan.module.P_Student;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


public class P_StudentDAO implements P_StudentService {

	// JDBC 접속
	private static final String DRV ="oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	// SQL문 작성
	
	private static final String insertSQL = 
			" insert into P_Student(no, name, age, addr, depart, sjno, regdate) values(stno.nextval, ?, ?, ?, ?, ?, sysdate) ";
	private static final String selectSQL = 
			" select no, name, depart, sjno from P_Student ";
	private static final String selectOneSQL = 
			" select * from P_Student where no = ? ";			// 상세 전체조회
	private static final String updateSQL = 
			" update P_Student set name = ?, age = ?, addr = ?, depart = ?, sjno = ?, regdate = sysdate where no = ? ";
	private static final String deleteSQL = 
			" delete from P_Student where no = ? ";
	
	@Override
	public int insertStudent(P_Student svo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;			// 몇건에 데이터가 저장되었는지 int로.. (??)
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			ps.setString(1, svo.getName());
			ps.setString(2, svo.getAge());					// age를 int로 선언했다면, ps.setInt로..
			ps.setString(3, svo.getAddr());
			ps.setString(4, svo.getDepart());
			ps.setString(5, svo.getSjno());		
			
			cnt = ps.executeUpdate();
			
		}catch(Exception ex){
			ex.printStackTrace();			
		}finally{
			if ( ps != null)
				try{ ps.close(); } catch (Exception e ) {}
			if ( conn != null)
				try { conn.close(); } catch (Exception e) {}
		}
		
		return cnt;
	}
	@Override
	public String selectStudent() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			while(rs.next()){						// (??)
				for(int i = 7; i<=7; ++i)
					result += rs.getString(i) + " ";
				result += "\n";
			}	
			
		}catch ( Exception ex){
			ex.printStackTrace();
		}finally{
			if( rs != null)
				try{ rs.close(); } catch ( Exception e) {}
			if ( ps != null)
				try{ ps.close(); } catch (Exception e ) {}
			if ( conn != null)
				try { conn.close(); } catch (Exception e) {}
		}
		return result;
	}
	@Override
	public String selectStudent(String no) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public int deleteStudent(String no) {
		// TODO Auto-generated method stub
		return 0;
	}
	@Override
	public int updateSudent(h_eekwan.module.P_Student svo) {
		// TODO Auto-generated method stub
		return 0;
	}

	
	
}
